<?php
/**
 * SpecialController class.
 * SpecialController is for the single special object.
 * 
 * LBB Solutions Ltd. 版权所有 @ 2012.
 */
class SpecialController extends ApiController
{
	/**
	 * Claim a special
	 * hid, key, id, number, expected_time
	 */
	public function actionClaim()
	{
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (empty($device_token) and empty($id)) {
			$this->_sendJSON(300, BUtils::T('no_login_reserve_special_msg'));
		}
		
		// get id_special
		$id_special = Yii::app()->request->getParam('id', null);
		if (empty($id_special)) $this->_sendError('The parameter "id" is missing!');
		
		// get expected_time: we don't support this feature in version 1
		$expected_time = null;
		$expected_ts = Yii::app()->request->getParam('expected_time', null);
		if (strlen($expected_ts) === 13) {
			$expected_time = date('Y-m-d H:i:s', (int)((float)$expected_ts / 1000));
			if ($expected_time === false) {
				$this->_sendError('The parameter expected_time='.$expected_ts.' is incorrect!');
			}
		}
		
		// get number=?, user select a reserve number
		$number = Yii::app()->request->getParam('number', 1);
		
		// set $params
		$params = array(
			':device_token' => $device_token,
			':id_user' => $id,
			':id_special' => $id_special,
		);
		$params_ins = array(
			':device_token' => $device_token,
			':id_user' => $id,
			':id_special' => $id_special,
			':expected_visit_time' => $expected_time,
			':number' => $number,
		);
		$logData = $params_ins;
		
		//insert claim
		$sql_claim_ins = 'INSERT INTO tbl_merchant_special_redeem (device_token, id_user, id_special, expected_visit_time, redeemed, number)
			VALUES(:device_token, :id_user, :id_special, :expected_visit_time, 1, :number)';
		//fetch status
		$sql_status = 'SELECT s.id, NULL as distance, s.max_number, s.show_hours, s.start_hour, s.end_hour, UNIX_TIMESTAMP(s.end_date) as end_date_ts, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num, '	// calculate today's redeemed_num
			. ' (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
				 WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
				 ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status, ' // user's redeemed_status today
			. ' NULL AS redeemed_time ' // user reserve the offer
			. ' FROM tbl_merchant_special s
			WHERE s.id=:id_special AND s.end_date>NOW()
			GROUP BY s.id';
		$connection = Yii::app()->db;
		//check if user can claim this special
		$command = $connection->createCommand($sql_status);
		$row = $command->queryRow(true, $params);
		
		$message = null;
		$error_response = null;
		if (empty($row)) {
			//1. no data means expried
			$message = "special/claim : [{$device_token}] user {$id} claims an expried offer {$id_special}.";
			self::APILogging($message, $logData, 'scan_mqr_error');
			$error_response = BUtils::T('special_expired_msg');
		} elseif (date('Gi') > $row['end_hour'] and time() > $row['end_date_ts']) {
			//1. check start hour and get hour
			$message = "special/claim : [{$device_token}] user {$id} claims an expried offer {$id_special} at " . date('H:i:s');
			self::APILogging($message, $logData, 'scan_mqr_error');
			$end_time = BUtils::GetEndHourFormat($row['end_hour']);	//get end time
			$error_response = BUtils::T('special_expired_time_msg', array('{time}'=>$end_time));
		} elseif ($row['redeemed_status'] > 0) {
			//2. check if re-claim for today, TODO: user can reserve 1 time per day. 2012.12.13
			$message = "special/claim : [{$device_token}] user {$id} re-claims an offer {$id_special}.";
			self::APILogging($message, $logData, 'scan_mqr_error');
			$error_response = BUtils::T('special_reclaim_msg');
		} elseif ($row['max_number'] > 0 and $row['max_number'] <= $row['claimed_num']) {
			//3. check if given out
			$message = "special/claim : [{$device_token}] user {$id} claims but no offer left.";
			self::APILogging($message, $logData, 'scan_mqr_error');
			$error_response = BUtils::T('special_given_out_msg');
		} elseif ($row['max_number'] > 0 and $number > $row['max_number'] - $row['claimed_num']) {
			//the input number > left number
			$number_left = $row['max_number'] - $row['claimed_num'];
			$message = "special/claim : [{$device_token}] user {$id} input number={$number} is larger than left offers={$number_left}.";
			self::APILogging($message, $logData, 'scan_mqr_error');
			$error_response = BUtils::T('special_claim_excess_msg', array('{number}'=>$number_left));
			//$params_ins[':number'] = $number_left;
		}
		
		$sql_logging = 'INSERT INTO tbl_merchant_special_redeem_log (device_token, id_user, id_special, action, message)
			VALUES (:device_token, :id_user, :id_special, :action, :message)';
		$msg_params = array(
			':device_token' => $device_token,
			':id_user' => $id,
			':id_special' => $id_special,
			':action' => 'claim',
			':message' => $message,
		);
		if (!empty($message)) {
			//logging message
			$command = $connection->createCommand($sql_logging);
			$command->execute($msg_params);
		}
		//stop and send error message
		if (!empty($error_response)) {
			$connection->active = false;
			$this->_sendError($error_response);
		}
		//insert claim data to DB
		$command = $connection->createCommand($sql_claim_ins);
		$command->execute($params_ins);
		//logging message
		$msg_params[':message'] = "special/claim : [{$device_token}] user {$id} claimed an offer {$id_special} with number={$number}.";
		$command = $connection->createCommand($sql_logging);
		$command->execute($msg_params);
		//fetch status info
		$command = $connection->createCommand($sql_status);
		$row = $command->queryRow(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkStatus($row));
	}
	
	/**
	 * Cancel a special (reserved)
	 * hid, key, id
	 */
	public function actionCancel()
	{
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (empty($device_token) and empty($id)) {
			$this->_sendError('The parameter token or hid is missing!');
		}
		
		// get id_special
		$id_special = Yii::app()->request->getParam('id', null);
		if (empty($id_special)) $this->_sendError('The parameter "id" is missing!');
		
		// set $params
		$params = array(
			':device_token' => $device_token,
			':id_user' => $id,
			':id_special' => $id_special,
		);
		
		$sql_special_cancel = 'DELETE FROM tbl_merchant_special_redeem
			WHERE id_special=:id_special AND (device_token=:device_token OR id_user=:id_user) AND DATE(insert_timestamp)=DATE(NOW()) ';
		//fetch status
		$sql_status = 'SELECT s.id, NULL as distance, s.max_number, s.show_hours, s.start_hour, s.end_hour, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num, '	// calculate today's redeemed_num
			. ' (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
				 WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
				 ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status, ' // user's redeemed_status today
			. ' NULL AS redeemed_time ' // user cancel the reserve
			. ' FROM tbl_merchant_special s
			WHERE s.id=:id_special
			GROUP BY s.id';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_special_cancel);
		$command->execute($params);
		//fetch status info
		$command = $connection->createCommand($sql_status);
		$row = $command->queryRow(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkStatus($row));
	}
	
	/**
	 * Redeem a special
	 * hid, key, id, mid
	 */
	public function actionRedeem()
	{
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (empty($device_token) and empty($id)) {
			$this->_sendError('The parameter "token" or "hid" is missing!');
		}
		
		// get id_special
		$id_special = Yii::app()->request->getParam('id', null);
		if (empty($id_special)) $this->_sendError('The parameter "id" is missing!');
		
		
		$connection = Yii::app()->db;
		// get id_merchant
		$mcode = Yii::app()->request->getParam('mcode', null);
		if (!empty($mcode)) {
			// redeem with qr-code
			$id_merchant = BUtils::parseMCodeToMId($mcode);
			if ($id_merchant < 10000) {
				$connection->active = false;
				$this->_sendError('The parameter "mcode"='.$mcode.' is incorrect!');
			} else {
				//1. check if this is a right merchant
				$sql_merchant_check = 'SELECT id FROM tbl_merchant_special WHERE id=:id_special AND id_merchant=:id_merchant';
				$command = $connection->createCommand($sql_merchant_check);
				$row = $command->queryRow(true, array(':id_special'=>$id_special, ':id_merchant'=>$id_merchant));
				if (empty($row)) {
					$message = "special/redeem : [{$device_token}] user {$id} redeems an offer {$id_special} with a wrong code={$mcode}.";
					self::APILogging($message, array(':id_special'=>$id_special, ':id_merchant'=>$id_merchant), 'scan_mqr_error');

					$sql_logging = 'INSERT INTO tbl_merchant_special_redeem_log (device_token, id_user, id_special, action, message) 
						VALUES (:device_token, :id_user, :id_special, :action, :message)';
					$msg_params = array(
						':device_token' => $device_token,
						':id_user' => $id,
						':id_special' => $id_special,
						':action' => 'redeem',
						':message' => $message,
					);
					//logging message
					$command = $connection->createCommand($sql_logging);
					$command->execute($msg_params);

					//end and error response
					$connection->active = false;
					$this->_sendError(BUtils::T('special_scan_wrong_code_msg'));
				}
			}
			$redeem_type = 0;
		} else {
			//redeem by click
			$redeem_type = 1;
		}
		
		$params = array(
			':device_token' => $device_token,
			':id_user' => $id,
			':id_special' => $id_special,
		);
		//insert redeem
		$sql_redeem_ins = 'INSERT INTO tbl_merchant_special_redeem (device_token, id_user, id_special, redeemed, redeemed_time)
			VALUES(:device_token, :id_user, :id_special, 2, NOW())';
		//update redeem
		$sql_redeem_upd = 'UPDATE tbl_merchant_special_redeem SET redeemed=2, redeemed_time=NOW(), id_user=:id_user
			WHERE id_special=:id_special AND (device_token=:device_token OR id_user=:id_user) AND DATE(insert_timestamp)=DATE(NOW())';//only for today
		//fetch status
		$sql_status = 'SELECT s.id, NULL as distance, s.max_number, s.week_days, s.show_hours, s.start_hour, s.end_hour, UNIX_TIMESTAMP(s.end_date) as end_date_ts, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num, '	// calculate today's redeemed_num
			. ' (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
				 WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
				 ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status, ' // user's redeemed_status today
			. ' (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
				 WHERE r4.id_special=s.id AND (r4.device_token=:device_token OR r4.id_user=:id_user) AND DATE(r4.insert_timestamp)=DATE(NOW())
				 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time ' // user's redeemed_time today
			. ' FROM tbl_merchant_special s
			WHERE s.id=:id_special AND s.end_date>NOW()
			GROUP BY s.id';
		//check if user can redeem this special
		$command = $connection->createCommand($sql_status);
		$row = $command->queryRow(true, $params);
		
		$message = null;
		$error_response = null;
		$now = time();
		$week_no = date('N');
		if (empty($row)) {
			//2. no data means expried
			$message = "special/redeem : [{$device_token}] user {$id} redeems an expried offer {$id_special}.";
			self::APILogging($message, $row, 'scan_mqr_error');
			$error_response = BUtils::T('special_expired_msg');
		} elseif (!empty($row['week_days']) and false === strpos($row['week_days'], $week_no)) {
			//0. check if not today
			$message = "special/redeem : [{$device_token}] user {$id} redeems an not today offer {$id_special} at " . date('H:i:s');
			self::APILogging($message. date('H:i:s'), $row, 'scan_mqr_error');
			$error_response = BUtils::T('special_not_open_today_msg');
		} elseif ($row['show_hours'] == 1 and $row['start_hour']<$row['end_hour'] and (date('Gi')<$row['start_hour'] or $now < $row['start_date_ts'])) {
			//1. check start hour
			$message = "special/redeem : [{$device_token}] user {$id} redeems an not start offer {$id_special} at " . date('H:i:s');
			self::APILogging($message. date('H:i:s'), $row, 'scan_mqr_error');
			$error_response = BUtils::T('special_not_start_msg');
		} elseif ($row['show_hours'] == 1 and $row['start_hour']<$row['end_hour'] and (date('Gi')>$row['end_hour'] or $now > $row['end_date_ts'])) {
			//2. check end hour
			$message = "special/redeem : [{$device_token}] user {$id} redeems an expried offer {$id_special} at " . date('H:i:s');
			self::APILogging($message. date('H:i:s'), $row, 'scan_mqr_error');
			$end_time = BUtils::GetEndHourFormat($row['end_hour']);//get end time
			$error_response = BUtils::T('special_expired_time_msg', array('{time}'=>$end_time));
		} elseif ($row['redeemed_status'] == 1) {
			//update if has claimed data today (redeemed_status=1)
			$command = $connection->createCommand($sql_redeem_upd);
			$command->execute($params);
			$message = "special/redeem : [{$device_token}] user {$id} redeemed an offer {$id_special} that has been claimed today.";
		} elseif ($row['redeemed_status'] == 2) {
			//We allow user re-redeem a special (redeemed_status=2), bocheng, 2012.12.01
			$command = $connection->createCommand($sql_redeem_upd);
			$command->execute($params);
			$message = "special/redeem : [{$device_token}] user {$id} redeemed an old offer {$id_special} that has been redeemed.";
		} else {
			//We allow user redeem a special even he/she didn't claim before, bocheng, 2012.12.01
			$command = $connection->createCommand($sql_redeem_ins);
			$command->execute($params);
			$message = "special/redeem : [{$device_token}] user {$id} redeemed an new offer {$id_special} that has not been claimed.";
		}
		
		$sql_logging = 'INSERT INTO tbl_merchant_special_redeem_log (device_token, id_user, id_special, action, message)
			VALUES (:device_token, :id_user, :id_special, :action, :message)';
		$msg_params = array(
			':device_token' => $device_token,
			':id_user' => $id,
			':id_special' => $id_special,
			':action' => 'redeem',
			':message' => $message,
		);
		if (!empty($message)) {
			//logging message
			$command = $connection->createCommand($sql_logging);
			$command->execute($msg_params);
		}
		//stop and send error message
		if (!empty($error_response)) {
			$connection->active = false;
			$this->_sendError($error_response);
		} else if (false) {
			//cancel the campaign email
			//2013.4.16: add the confirmation email for campaign
			//send email if this user is subscribed
			$sql_user_subscribe = 'SELECT u.name, u.email, u.lang, s.id_user, s.subscribed_time, s.unsubscribed_time 
				FROM tbl_user_account u
				LEFT JOIN tbl_user_subscribe s ON s.id_user=u.id AND s.type=1
				WHERE u.id=:id ';
			$command = $connection->createCommand($sql_user_subscribe);
			$subscribe = $command->queryRow(true, array(':id' => $id));
			if (empty($subscribe['id_user'])) {
				$sql_subscribe_add = 'INSERT INTO tbl_user_subscribe (id_user, type) VALUES (:id_user, 1) ';
				$command = $connection->createCommand($sql_subscribe_add);
				$command->execute(array(':id_user' => $id));
			}
			//send email
			if (empty($subscribe['unsubscribed_time'])) {
				//get merchant info
				$sql_merchant = 'SELECT m.name, m.id
					FROM tbl_merchant_account m
					INNER JOIN tbl_merchant_special s ON s.id_merchant=m.id
					WHERE s.id=:id_special ';
				$command = $connection->createCommand($sql_merchant);
				$merchant = $command->queryRow(true, array(':id_special' => $id_special));
				
				$this->_sendRedeemedEmail($subscribe['email'], $subscribe['name'], $merchant['name'], $merchant['id'], $subscribe['lang']);
			}
		}
		
		//fetch status info
		$command = $connection->createCommand($sql_status);
		$row = $command->queryRow(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkStatus($row));
	}
	
	/**
	 * Get the current status of special
	 */
	public function actionStatus()
	{
		// set the limit number and offset
		$id_special = Yii::app()->request->getParam('id', null);
		if (empty($id_special)) $this->_sendError('The parameter "id" is missing!');
		
		$params = array(
			':id_special' => $id_special,
		);
		
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		$s_status = null;
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (!empty($device_token) or !empty($id)) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
					 WHERE r4.id_special=s.id AND (r4.device_token=:device_token OR r4.id_user=:id_user) AND DATE(r4.insert_timestamp)=DATE(NOW())
					 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time '; // user's redeemed_time today
			$params[':id_user'] = $id;
			$params[':device_token'] = $device_token;
		}
		
		// fetch special
		// only looking for today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
		$sql_status = 'SELECT s.id, ' . $s_distance
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . '
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.id=:id_special
			GROUP BY s.id';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_status);
		$row = $command->queryRow(true, $params);
		
		//+1 viewed
		$userId = empty($id) ? null : $id;
		$ipAddress = Yii::app()->request->getUserHostAddress();
		$sql_insert = 'INSERT INTO tbl_merchant_special_viewed (device_token, id_user, id_special, ip, agent, action) VALUES (:device_token, :id_user, :id_special, :ip, :agent, :action)';
		$command = $connection->createCommand($sql_insert);
		$command->execute(array(
			':device_token'=>$device_token,
			':id_user'=>$userId,
			':id_special'=>$id_special,
			':ip'=>$ipAddress,
			':agent'=>Yii::app()->request->getUserAgent(),
			':action'=>1,//1 - get status
		));
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkStatus($row));
	}
	
	/**
	 * API: special/view
	 */
	public function actionView()
	{
		// set the limit number and offset
		$id_special = Yii::app()->request->getParam('id', null);
		if (empty($id_special)) $this->_sendError('The parameter "id" is missing!');
		
		$params = array(
			':id_special' => $id_special,
		);
		
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		$s_status = null;
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (!empty($device_token) or !empty($id)) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
					 WHERE r4.id_special=s.id AND (r4.device_token=:device_token OR r4.id_user=:id_user) AND DATE(r4.insert_timestamp)=DATE(NOW())
					 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time
					, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
					   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';// add the following data
			$params[':id_user'] = $id;
			$params[':device_token'] = $device_token;
		}
		
		// fetch special
		$sql_special = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . '
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.id=:id_special
			GROUP BY s.id';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_special);
		$row = $command->queryRow(true, $params);
		
		//+1 viewed
		$userId = empty($id) ? null : $id;
		$sql_insert = 'INSERT INTO tbl_merchant_special_viewed (device_token, id_user, id_special, ip, agent, action) VALUES (:device_token, :id_user, :id_special, :ip, :agent, :action)';
		$command = $connection->createCommand($sql_insert);
		$command->execute(array(
			':device_token'=>$device_token,
			':id_user'=>$userId,
			':id_special'=>$id_special,
			':ip' => Yii::app()->request->getUserHostAddress(),
			':agent' => Yii::app()->request->getUserAgent(),
			':action'=>2,//2 - get view
		));
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecial($row));
	}
	
	/**
	 * Make a special array.
	 * @param array $r
	 * @return array
	 */
	protected function _mkSpecial($r) {
		if (empty($r['id'])) {
			return null;
		}
		
		$norm_special = BUtils::T('special_lbl');
		
		$week_array = BUtils::WeekDayKeys(7, true);
		$categoriesIndex = BUtils::CategoriesNo();
		
		//set the day string
		$week_string = self::GetWeekdays($r['week_days'], $week_array);
		$range_date = self::GetRecentDayName($r['week_days'], $week_string, ($this->action->id == 'next24'), $r['start_hour']);
		$period_string = BUtils::GetUntilDate($r['uts_end_date']);

		//set the valid_time
		$range_time = BUtils::GetHourRangeFormat($r['start_hour'], $r['end_hour'], $r['show_hours']);

		//logo
		$url_logo = null;
		if (!empty($r['url_logo'])) {
			$url_logo = 'http://yamii.fi/business' . $r['url_logo'];
		}

		//set offer img
		$url_thumb = 'http://yamii.fi/img/default_special_thumb.png';
		$url_picture = 'http://yamii.fi/img/default_special_picture.png';
		if (!empty($r['url_picture'])) {
			$url_thumb = 'http://yamii.fi/business' . $r['url_picture'];
			$url_picture = 'http://yamii.fi/business' . str_replace('thumb_', '', $r['url_picture']);
		} elseif (!empty($r['url_offer_img'])) {
			$url_thumb = 'http://yamii.fi/business' . $r['url_offer_img'];
			$url_picture = 'http://yamii.fi/business' . str_replace('thumb_', '', $r['url_offer_img']);
		}

		//following
		if (empty($r['following']) or $r['following'] == 0) {
			$following = 0;
		} else {
			$following = 1;
		}

		//set price and discount
		if (empty($r['discount'])) {
			$r['discount'] = null;
		} else {
			$r['discount'] = round($r['discount'], 2);
		}
		if (empty($r['price'])) $price = null;
		else $price = (float)$r['price'];
		if (empty($r['value'])) $value = null;
		else $value = (float)$r['value'];
		if (empty($r['discount'])) $discount = null;
		else $discount = (float)$r['discount'];
		$highlight = $r['highlight'];

		//set category
		if (empty($r['category_key'])) {
			$category = '';
		} else $category = BUtils::T($r['category_key']);
		$cate_no = (int)$categoriesIndex[$r['category_key']];

		//set new flag
		$new_flag = $hot_flag = 0;
		if (time() - $r['uts_start_date'] < 12*3600) {
			$new_flag = 1;
		}
		
		//set redeem type
		$redeem_type = (int)$r['redeem_type'];
		return array(
			'id' => $r['id'],
			'url_thumb' => $url_thumb,
			'url_picture' => $url_picture,
			'new_flag' => $new_flag,//new
			'hot_flag' => $hot_flag,//new
			'title' => $norm_special,
			'content' => $r['content'],
			'price' => $price,
			'value' => $value,
			'discount' => $discount,
			'highlight' => $highlight,//new
			'currency' => $r['currency_code'],
			'range_time' => $range_time,
			'range_date' => $range_date,
			'week' => $week_string,
			'period' => $period_string,//new
			'start_time' => (int)$r['uts_start_date'],
			'end_time' => (int)$r['uts_end_date'],
			'published_time' => (int)$r['uts_published_time'],
			'max_num' => (int)$r['max_number'],
			'redeem_type' => $redeem_type,//new
			'claimed_num' => (int)$r['claimed_num'],
			'redeemed_num' => (int)$r['redeemed_num'],
			'status' => empty($r['redeemed_status']) ? 0 : (int)$r['redeemed_status'],
			'redeemed_time' => (int)$r['redeemed_time'],
			'merchant' => array(
				'id' => $r['id_merchant'],
				'name' => $r['name'],
				'category' => $category,
				'category_no' => $cate_no,
				'url_logo' => $url_logo,
				'area' => empty($r['biz_area']) ? '' : $r['biz_area'],
				'address' => empty($r['address']) ? '' : $r['address'],
				'zip' => $r['postcode'],
				'city' => $r['city'],
				'phone' => MerchantObject::formatPhoneNumber($r['phone']),
				'distance' => empty($r['distance']) ? null : (float)$r['distance'],
				'lat' => (float)$r['latitude'],
				'lng' => (float)$r['longitude'],
				'following' => $following,
			),
		);
	}
	
	/**
	 * Make a special status.
	 * @param array $r
	 * @return array
	 */
	protected function _mkStatus($r) {
		if (empty($r['id'])) {
			return null;
		}
		
		return array(
			'id' => $r['id'],
			'distance' => empty($r['distance']) ? null : (float)$r['distance'],
			'claimed_num' => (int)$r['claimed_num'],
			'redeemed_num' => (int)$r['redeemed_num'],
			'status' => (int)$r['redeemed_status'],
			'redeemed_time' => (int)$r['redeemed_time'],
		);
	}
	
	protected function _sendRedeemedEmail($user_email, $user_name, $merchant_name, $merchant_id, $lang = 'en_gb') {
		$merchant_url = Yii::app()->createAbsoluteUrl('restaurant/view', array(
			'id' => $merchant_id,
			'utm_content' => $user_email,
			'utm_source' => 'yamii_fi',
			'utm_medium' => 'email',
			'utm_campaign' => 'youtastewepay',
		));
		
		$text_body['en'] = <<<TEXT
Dear {$user_name},

Thank you for choosing Yamii - Your Deal Radar to get special offers!

This letter is to confirm that you are participating in our Raffle campaign "You Taste, We Pay", since you just unlocked a special offer at {$merchant_name} with Yamii.
You can find the detail information of the campaign through: http://bit.ly/ZwEajX.

Yamii is a brand new APP helps you discover the great deals based on your location and preference.
If you like the idea, please kindly like us on facebook: http://on.fb.me/16bgrtJ. That will definitely inspire us to get more local merchants for variety deals.

Thanks again and hope you will be the winner to get a free dinner!

Best regards
Yamii Team
TEXT;
		$html_body['en'] = <<<HTML
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Offer Redeemed</title>
</head>
<body style="font-size:13px;font-family:Arial;">
Dear {$user_name},<br />
<br />
Thank you for choosing Yamii - Your Deal Radar to get special offers!
This letter is to confirm that you are participating in our Raffle campaign "<a href="http://bit.ly/ZwEajX" target="_blank">You Taste, We Pay</a>",
since you just unlocked a special offer at <a href="{$merchant_url}" target="_blank">{$merchant_name}</a> with Yamii.
You can find the detail information of the campaign through: http://bit.ly/ZwEajX.<br />
<br />
Yamii is a brand new APP helps you discover the great deals based on your location and preference.
If you like the idea, please kindly like us on facebook: <a href="http://on.fb.me/16bgrtJ" target="_blank">http://on.fb.me/16bgrtJ</a>.
That will definitely inspire us to get more local merchants for variety deals.<br />
<br />
Thanks again and hope you will be the winner to get a free dinner!<br />
<br />
Best regards<br />
Yamii Team<br />
</body>
<html>
HTML;

		
		$text_body['fi'] = <<<TEXT
Hei {$user_name},

Kiitos, että valitsit Yamii-Tarjoustutkasi saadakse erikoistarjouksia!
Vahvistamme, että olet jo mukana meidän arvonnassa kampanjassa "Sinä maistat, me maksamme", kun käytit Yamii tarjousta "{$merchant_name}" ravintolassa.
Löydät lisää ​tietoa kampanjasta tätä kautta: http://bit.ly/ZwEajX.

Yamii on upouusi APP, joka auttaa sinua löytämään tarjouksia sijaintisi ja mieltymystesi mukaan.
Jos pidät ajatuksetamme, tykkää meistä facebookissa: http://on.fb.me/16bgrtJ.

Kiitos vielä kerran ja onnea arvontaan!

Ystävällisin Terveisin
Yamii tiimi
TEXT;
		$html_body['fi'] = <<<HTML
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Offer Redeemed</title>
</head>
<body style="font-size:13px;font-family:Arial;">
Hei {$user_name},<br />
<br />
Kiitos, että valitsit Yamii-Tarjoustutkasi saadakse erikoistarjouksia!
Vahvistamme, että olet jo mukana meidän arvonnassa kampanjassa "<a href="http://bit.ly/ZwEajX" target="_blank">Sinä maistat, me maksamme</a>",
kun käytit Yamii tarjousta "<a href="{$merchant_url}" target="_blank">{$merchant_name}</a>" ravintolassa.
Löydät lisää ​tietoa kampanjasta tätä kautta: http://bit.ly/ZwEajX.<br />
<br />
Yamii on upouusi APP, joka auttaa sinua löytämään tarjouksia sijaintisi ja mieltymystesi mukaan.
Jos pidät ajatuksetamme, tykkää meistä facebookissa: <a href="http://on.fb.me/16bgrtJ" target="_blank">http://on.fb.me/16bgrtJ</a>.<br />
<br />
Kiitos vielä kerran ja onnea arvontaan!<br />
<br />
Ystävällisin Terveisin<br />
Yamii tiimi<br />
</body>
<html>
HTML;
		
		$subject['fi'] = 'Sinä maistat, me maksamme - Yamii.fi';
		$subject['en'] = 'You Taste, We Pay - Yamii.fi';
		
		$lang = strtolower(substr($lang, 0, 2));
		if ($lang == 'fi') {
			BUtils::SendEmailOut($subject['fi'], array($user_email), $html_body['fi'], $text_body['fi']);
		} else {
			BUtils::SendEmailOut($subject['en'], array($user_email), $html_body['en'], $text_body['en']);
		}
	}
}